﻿using ClosedXML.Excel;
using QMERP.Infrastructure.Extensions;
using QMERP.Infrastructure.Tools;

namespace QMERP.Domain.Dtos
{
    public static class LensViewResponseExtension
    {
        // <summary>
        /// lensViews转换XY表Stream
        /// </summary>
        /// <param name="lensViews"></param>
        /// <returns></returns>
        public static MemoryStream ToXYExcelStream(this List<LensViewResponse> lensViews)
        {
            var workbook = new XLWorkbook();
            foreach (var lensView in lensViews)
            {
                SetXLWorkbook(workbook, lensView);
            }
            MemoryStream ms = new();
            workbook.SaveAs(ms);
            workbook.Dispose();
            ms.Flush();
            //ms.Position = 0;
            return ms;
            //return null;
        }
        // <summary>
        /// lensView转换XY表Stream
        /// </summary>
        /// <param name="lensView"></param>
        /// <returns></returns>
        public static MemoryStream ToXYExcelStream(this LensViewResponse lensView)
        {
            var workbook = new XLWorkbook();
            SetXLWorkbook(workbook, lensView);
            MemoryStream ms = new();
            workbook.SaveAs(ms);
            workbook.Dispose();
            ms.Flush();
            //ms.Position = 0;
            return ms;
        }
        private static void SetXLWorkbook(XLWorkbook workbook, LensViewResponse lensView)
        {
            List<LensDetailResponse> lensDetails = new();
            for (int sph = lensView.Lens.MinSPH; sph <= lensView.Lens.MaxSPH; sph += 25)
            {
                string degreesType = "-/-";
                if (sph > 0)
                    degreesType = "+/-";
                for (int cyl = lensView.Lens.MinCYL; cyl <= lensView.Lens.MaxCYL; cyl += 25)
                {
                    if (lensDetails.Any(x => x.SPH == sph && x.CYL == cyl))
                        continue;
                    int qty = lensView.LensDetails.FirstOrDefault(x => x.SPH == sph && x.CYL == cyl)?.Qty ?? 0;
                    LensDetailResponse detail = new()
                    {
                        DegreesType = degreesType,
                        SPH = sph,
                        CYL = cyl,
                        Qty = qty,
                    };
                    lensDetails.Add(detail);
                }
            }
            List<string> degreesTypes = lensDetails.Select(x => x.DegreesType).Distinct().ToList();
            foreach (var degreesType in degreesTypes)
            {
                var list = lensDetails.Where(x => x.DegreesType == degreesType).ToList();
                var cylTitles = list.Select(s => s.CYL).OrderBy(x => Math.Abs(x)).Distinct().ToList();
                var sphTitles = list.Select(s => s.SPH).OrderBy(x => Math.Abs(x)).Distinct().ToList();
                //string lensCode = list[0].LensCode;
                string degrees = degreesType.Substring(0, 1);
                string sheetName = string.Concat(lensView.Lens.SalesLensCode, degrees);
                if (lensView.Lens.SalesLensCode.EndsWith('+') || lensView.Lens.SalesLensCode.EndsWith('-'))
                {
                    sheetName = lensView.Lens.SalesLensCode;
                }
                var sheet = workbook.AddWorksheet(sheetName);
                //IDataFormat dataformat = workbook.CreateDataFormat();
                //short gs = dataformat.GetFormat("[=0]\"\"");
                //ICellStyle style = workbook.CreateCellStyle();
                //style.DataFormat = gs;

                //第一行 表头
                var rowHeader = sheet.Row(1);
                rowHeader.Cell(1).SetValue(lensView.Warehouse.Code);//哪个仓库
                rowHeader.Cell(3).SetValue(sheetName);//镜种
                rowHeader.Cell(5).SetValue(DateTime.Now.ToShortDateString());//日期

                int r = 3;
                foreach (var sphTitle in sphTitles)
                {
                    var yRow = sheet.Row(r);
                    string sph = "S" + degrees + Math.Abs(sphTitle).ToString().PadLeft(3, '0');
                    yRow.Cell(1).SetValue(sph);
                    r++;
                }
                sheet.Row(r).Cell(1).SetValue("Total");
                var stRow1 = sheet.Row(2);
                stRow1.Cell(1).SetValue("SPH");
                int c = 2;
                foreach (var cylTitle in cylTitles)
                {
                    string cyl = "C" + Math.Abs(cylTitle).ToString().PadLeft(3, '0');
                    stRow1.Cell(c).SetValue(cyl);
                    //X加总行，若CYL变化，此处的公式也需修改
                    sheet.Row(r).Cell(c).SetFormulaA1($"Sum({c.ToColumnChar()}3:{c.ToColumnChar()}{r - 1})");
                    c++;
                }
                sheet.Row(r).Cell(c).SetFormulaA1($"Sum({c.ToColumnChar()}3:{c.ToColumnChar()}{r - 1})");
                stRow1.Cell(c).SetValue("Total");
                //IRow stRowF = sheet.CreateRow(c);
                //stRowF.CreateCell(0).SetCellValue("Total");
                string colStr = Convert.ToString((char)((int)'A' + cylTitles.Count));
                for (int i = 0; i < sphTitles.Count; i++)
                {
                    int sph = sphTitles[i];
                    //IRow stRowN = sheet.CreateRow(i + 2);
                    for (int j = 0; j < cylTitles.Count; j++)
                    {
                        int cyl = cylTitles[j];
                        var cellN = sheet.Row(i + 3).Cell(j + 2);
                        var l = list.Where(x => x.SPH == sph && x.CYL == cyl).FirstOrDefault();
                        if (l != null)
                        {
                            //if (l.Qty > 0)
                            //{
                            //    cellN.SetCellValue(l.Qty);
                            //}
                            cellN.SetValue(l.Qty);
                        }
                    }
                    //Y加总列，若CYL变化，此处的公式也需修改
                    sheet.Row(i + 3).Cell(c).SetFormulaA1($"Sum(B{i + 3}:{(c - 1).ToColumnChar()}{i + 3})");
                    //X加总行，若CYL变化，此处的公式也需修改
                    //stRowF.CreateCell(r).SetCellFormula("Sum(" + gscolStr + "3:" + gscolStr + "" + (r - 1) + ")");
                }
            }
        }
        /// <summary>
        /// XYStream表转LensViews
        /// </summary>
        /// <param name="warehouseCode"></param>
        /// <param name="lensCode"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static List<LensViewResponse> ToLensViews(this Stream stream)
        {
            var workbook = stream.ToXLWorkbook();
            List<LensViewResponse> list = new();
            for (int num = 1; num < workbook.Worksheets.Count; num++)
            {
                var sheet = workbook.Worksheet(num);
                ExcelHelper.IsXYTable(sheet);
                LensViewResponse lensViewResponse = new();
                string warehouseCode = sheet.Row(0).Cell(0).GetString();
                string lensCode = sheet.Row(0).Cell(2).GetString();
                if (lensCode.EndsWith('+') || lensCode.EndsWith('-'))
                {
                    lensCode = lensCode.Substring(0, lensCode.Length - 1);
                }
                lensViewResponse.Warehouse.Code = warehouseCode;
                lensViewResponse.Lens.SalesLensCode = lensCode;

                string degreesType = sheet.Name.Substring(sheet.Name.Length - 1) + "/-";
                var headerRow = sheet.Row(2);//第2行为标题行  
                int cellCount = sheet.LastCellUsed().Address.ColumnNumber;
                int rowCount = sheet.LastRowUsed().RowNumber();
                for (int i = 2; i < cellCount; i++)
                {
                    for (int j = 3; j < rowCount; j++)
                    {
                        var row = sheet.Row(j);
                        string sph = row.Cell(1).GetString();
                        string cyl = headerRow.Cell(i).GetString();
                        string qty = row.Cell(i).GetString();
                        LensDetailResponse model = new()
                        {
                            DegreesType = degreesType,
                            SPH = sph.Substring(1).ToInt(),
                            CYL = cyl.Substring(1).ToInt(),
                            Qty = qty == "" ? 0 : qty.ToInt()
                        };
                        lensViewResponse.LensDetails.Add(model);
                    }
                }
                list.Add(lensViewResponse);
            }
            return list;
        }
    }
}
